698~706
병렬 order by ,group by
정렬은(order by 절) temp tablespace 를 사용하므로 해당 유저가 어떤 temp tablespace 를 사용하는지 파악 후 작업 필요
select username , default_tablespace, temporary_tablespace from dba_users;
병렬작업엔 분배가 필요하며 그걸 QC 가 실행
병렬처리 사용 예
select /*\+ parallel(emp 2) \*/ sum(sal) ,deptno from emp group by deptno
SUM(SAL) DEPTNO
\---\--\--\--\- \--\--\
-\-
9400 30
10875 20
8750 10
Execution Plan
\
-\-
Plan hash value: 3475411915
\
\-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
\
\-
| 0 | SELECT STATEMENT | | 3 | 21 | 3 (34)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 3 | 21 | 3 (34)| 00:00:01 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH GROUP BY | | 3 | 21 | 3 (34)| 00:00:01 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 15 | 105 | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 15 | 105 | 2 (0)| 00:00:01 | Q1,00 | P->P | HASH |
| 6 | PX BLOCK ITERATOR | | 15 | 105 | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 7 | TABLE ACCESS FULL| EMP | 15 | 105 | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
\
\-
Statistics
\
-\-
12 recursive calls
0 db block gets
18 consistent gets
0 physical reads
0 redo size
688 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
병렬힌트 미사용시
SUM(SAL) DEPTNO
\---\--\--\--\- \--\--\
-\-
9400 30
10875 20
8750 10
Execution Plan
\
-\-
Plan hash value: 4067220884
\
--\-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
\
--\-
| 0 | SELECT STATEMENT | | 3 | 21 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMP | 15 | 105 | 3 (0)| 00:00:01 |
\
--\-
Statistics
\
-\-
1 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
688 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed